package com.liaoct.lct.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.liaoct.lct.util.Util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DataBaseManagement {

	private String dataBaseName = "mycontacts";

	private Context context;

	private DataBaseOpenHelper helper;

	private SQLiteDatabase sqlLite;

	private DataBaseManagement(Context context) {
		this.context = context;
	}

	public static DataBaseManagement newInstance(Context context) {
		return new DataBaseManagement(context);
	}

	public SQLiteDatabase openConnection(String dataBaseName) {
		this.dataBaseName = dataBaseName;
		helper = new DataBaseOpenHelper(context, dataBaseName, 3);
		sqlLite = helper.getWritableDatabase();
		return sqlLite;
	}

	public SQLiteDatabase openConnection() {
		helper = new DataBaseOpenHelper(context, dataBaseName, 3);
		sqlLite = helper.getWritableDatabase();
		return sqlLite;
	}

	public void closeConnection() {
		sqlLite.close();
	}

	public List<Map<String, Object>> queryContactList(String contactName) {
		this.openConnection();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" t1.*, ");
		sql.append(" t2.data, ");
		sql.append(" CASE WHEN t3.phone_type_id = 1 THEN t3.phone_number END AS mobile_number, ");
		sql.append(" CASE WHEN t3.phone_type_id = 2 THEN t3.phone_number END AS home_number, ");
		sql.append(" CASE WHEN t3.phone_type_id = 3 THEN t3.phone_number END AS company_number ");
		sql.append(" FROM  contacts_info AS t1 LEFT JOIN  photo_info AS t2   ON t1.photo_id = t2.id ");
		sql.append(" LEFT JOIN  (SELECT t1.*,t2.name FROM contacts_phone_info AS t1, phone_type_info AS t2 WHERE t1.phone_type_id = t2.id) AS t3  ON t1.id = t3.contacts_id ");
		sql.append(" WHERE 1=1 ");
		if (Util.isNotNull(contactName)) {
			sql.append(" AND t1.name like '%");
			sql.append(contactName);
			sql.append("%'");
		}
		sql.append("GROUP BY t1.id ORDER BY t1.first_letter");

		Cursor cursor = this.sqlLite.rawQuery(sql.toString(), new String[] {});
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (null != cursor) {
			while (cursor.moveToNext()) {
				Map<String, Object> map = new HashMap<String, Object>();
				String[] columns = cursor.getColumnNames();
				for (String column : columns) {
					Log.i("column", column);
					if ("data".equals(column)) {
						map.put(column,
								cursor.getBlob(cursor.getColumnIndex(column)));
					} else {
						map.put(column,
								cursor.getString(cursor.getColumnIndex(column)));
					}
				}
				list.add(map);
			}
		}
		this.closeConnection();
		return list;
	}

	public Map<String, Object> queryContactById(String id) {
		Map<String, Object> map = new HashMap<String, Object>();
		if (!Util.isNotNull(id)) {
			return map;
		}
		this.openConnection();

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" t1.*, ");
		sql.append(" t2.data, ");
		sql.append(" CASE WHEN t3.phone_type_id = 1 THEN t3.phone_number END AS mobile_number, ");
		sql.append(" CASE WHEN t3.phone_type_id = 2 THEN t3.phone_number END AS home_number, ");
		sql.append(" CASE WHEN t3.phone_type_id = 3 THEN t3.phone_number END AS company_number ");
		sql.append(" FROM  contacts_info AS t1 LEFT JOIN  photo_info AS t2   ON t1.photo_id = t2.id ");
		sql.append(" LEFT JOIN  (SELECT t1.*,t2.name FROM contacts_phone_info AS t1, phone_type_info AS t2 WHERE t1.phone_type_id = t2.id) AS t3  ON t1.id = t3.contacts_id ");
		sql.append(" WHERE t1.id = ");
		sql.append(id);
		sql.append(" GROUP BY t1.id ORDER BY t1.first_letter");

		Cursor cursor = this.sqlLite.rawQuery(sql.toString(), new String[] {});
		if (null != cursor) {
			while (cursor.moveToNext()) {
				String[] columns = cursor.getColumnNames();
				for (String column : columns) {
					Log.i("column", column);
					if ("data".equals(column)) {
						map.put(column,
								cursor.getBlob(cursor.getColumnIndex(column)));
					} else {
						map.put(column,
								cursor.getString(cursor.getColumnIndex(column)));
					}
				}
			}
		}
		this.closeConnection();
		return map;
	}

	public void dropDataBase() {
		StringBuffer sql = new StringBuffer();
		sql.append(" DROP DATABASE IF EXISTS ");
		sql.append(this.dataBaseName);
	}

	public void addContact(Map<String, Object> map, Map<String, Object> map2) {
		ContentValues content = new ContentValues();
		String sql = "INSERT INTO [contacts_info] ([id], [name], [photo_id], [times_contacted], [last_time_contacted], [company], [email], [address], [web], [first_letter]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String sql2 = "INSERT INTO [contacts_phone_info] ([id], [contacts_id], [phone_number], [phone_type_id]) VALUES (?, ?, ?, ?)";
	}

}
